package com.raja.servingStation.dao;

import com.alibaba.fastjson.JSONObject;
import com.raja.servingStation.common.constant.Constants;
import com.raja.servingStation.common.constant.MysqlPageVo;
import com.raja.servingStation.datasources.RepairSql2oTemp;
import com.raja.servingStation.entity.Engineer;
import com.raja.servingStation.entity.IdAndAvg;
import com.raja.servingStation.entity.OrderByEngineer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.math.BigDecimal;
import java.util.List;


/**
 * 公文Dao
 * on 2017/9/30.
 */
@Repository
public class EngineerDao {
    private static final Logger log = LoggerFactory.getLogger(EngineerDao.class);

    @Autowired
    private RepairSql2oTemp sql2oTemp;




    /**
     * 公文列表总数
     * @return
     */
    public Long getEngineerCount() {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(ID) from t_station_engineer");
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            return (Long)query.executeScalar();
        }
    }


    /**
     * 公文列表 List
     * @param pageVO
     * @return
     * @throws Exception
     */
    public List<Engineer> getEngineerList(MysqlPageVo pageVO) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from t_station_engineer ");
        String sqlfy = Constants.mysql_page_pre_sql+sql.toString()+Constants.mysql_page_end_sql;
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sqlfy);
            query.addParameter("limit",pageVO.getPageSize().intValue());
            query.addParameter("offset",pageVO.getOffset().intValue());
            List<Engineer> engineers = query.setAutoDeriveColumnNames(true).executeAndFetch(Engineer.class);
            return engineers;
        }
    }

    public List<IdAndAvg> getAvgScore() throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select t1.id as id,AVG(t2.AVERAGE_EVALUATE) as avg from t_station_engineer t1 left join t_repair_order t2 on t1.id=t2.stationEngineerId GROUP BY t1.ID ");
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            return query.setAutoDeriveColumnNames(true).executeAndFetch(IdAndAvg.class);
        }
    }

    public Long getCountOrdersByEngineerId(String engineerId,Integer stationId) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(t1.ORDER_ID) from t_repair_order t1 where t1.stationEngineerId =:engineerId and t1.REPAIR_TYPE = 2 and t1.REPAIR_ID = :stationId ");
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            query.addParameter("engineerId",engineerId);
            query.addParameter("stationId",stationId);
            return (Long)query.executeScalar();
        }
    }

    public List<OrderByEngineer> getOrdersByEngineerId(String engineerId,Integer stationId) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select t1.ORDER_ID orderId,t1.ORDER_CODE orderCode,t1.CAR_NO carNo,t1.UPDATE_TIME overTime,t1.DRIVER_NAME driverName,t1.DRIVER_PHONE driverPhone,t1.AVERAGE_EVALUATE avgScore " +
                " from t_repair_order t1 where t1.stationEngineerId =:engineerId and t1.REPAIR_TYPE = 2 and t1.REPAIR_ID = :stationId ");
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            query.addParameter("engineerId",engineerId);
            query.addParameter("stationId",stationId);
            return query.setAutoDeriveColumnNames(true).executeAndFetch(OrderByEngineer.class);
        }
    }

    public BigDecimal getMoneyByOrderId(String orderId) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select sum(t3.COMPONENT_PRICE) from t_repair_plan t3  where  t3.ORDER_ID = :orderId ");
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            query.addParameter("orderId",orderId);
            return (BigDecimal)query.executeScalar();
        }
    }

    public Object addEngineer(JSONObject body){
        String sql = "insert into t_station_engineer(NAME,LEVEL,TEL) values(:name,:level,:tel)";
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            Query query2 = con.createQuery("select max(id) from t_station_engineer ");
            query.addParameter("name", body.getString("name"));
            query.addParameter("level", body.getString("level"));
            query.addParameter("tel", body.getString("tel"));
            query.executeUpdate();
            return query2.executeScalar();
        }
    }


}
